package com.edu.dao;

import com.edu.bean.User;
import com.edu.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    /**
     * 根据用户名和密码查询用户
     */
    public User findByUsernameAndPassword(String username, String password, Integer roleId) {
        String sql = "SELECT * FROM user WHERE username = ? AND password = ? AND role_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, username);
            stmt.setString(2, password);
            stmt.setInt(3, roleId);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return convertToUser(rs);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据ID查询用户
     */
    public User findById(Integer userId) {
        String sql = "SELECT * FROM user WHERE user_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return convertToUser(rs);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 查询所有用户
     */
    public List<User> findAll() {
        String sql = "SELECT * FROM user ORDER BY user_id DESC";
        List<User> userList = new ArrayList<>();

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                userList.add(convertToUser(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userList;
    }

    /**
     * 分页查询用户
     */
    public List<User> findByPage(String username, int start, int pageSize) {
        String sql = "SELECT * FROM user";
        List<Object> params = new ArrayList<>();

        if (username != null && !username.trim().isEmpty()) {
            sql += " WHERE username LIKE ?";
            params.add("%" + username.trim() + "%");
        }

        sql += " ORDER BY user_id DESC LIMIT ?, ?";
        params.add(start);
        params.add(pageSize);

        List<User> userList = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                userList.add(convertToUser(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userList;
    }

    /**
     * 查询总记录数
     */
    public int findTotalCount(String username) {
        String sql = "SELECT COUNT(*) FROM user";
        List<Object> params = new ArrayList<>();

        if (username != null && !username.trim().isEmpty()) {
            sql += " WHERE username LIKE ?";
            params.add("%" + username.trim() + "%");
        }

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 添加用户
     */
    public boolean add(User user) {
        String sql = "INSERT INTO user (username, password, role_id, real_name, email, create_time) " +
                "VALUES (?, ?, ?, ?, ?, NOW())";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, user.getUsername());
            stmt.setString(2, user.getPassword());
            stmt.setInt(3, user.getRoleId());
            stmt.setString(4, user.getRealName());
            stmt.setString(5, user.getEmail());

            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 更新用户
     */
    public boolean update(User user) {
        StringBuilder sql = new StringBuilder("UPDATE user SET username = ?, role_id = ?, real_name = ?, email = ?, update_time = NOW()");
        if (user.getPassword() != null && !user.getPassword().trim().isEmpty()) {
            sql.append(", password = ?");
        }
        sql.append(" WHERE user_id = ?");

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql.toString())) {

            stmt.setString(1, user.getUsername());
            stmt.setInt(2, user.getRoleId());
            stmt.setString(3, user.getRealName());
            stmt.setString(4, user.getEmail());

            if (user.getPassword() != null && !user.getPassword().trim().isEmpty()) {
                stmt.setString(5, user.getPassword());
                stmt.setInt(6, user.getUserId());
            } else {
                stmt.setInt(5, user.getUserId());
            }

            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 删除用户
     */
    public boolean delete(Integer userId) {
        String sql = "DELETE FROM user WHERE user_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 检查用户名是否存在
     */
    public boolean checkUsernameExists(String username, Integer excludeUserId) {
        String sql = "SELECT COUNT(*) FROM user WHERE username = ?";
        List<Object> params = new ArrayList<>();
        params.add(username);

        if (excludeUserId != null) {
            sql += " AND user_id != ?";
            params.add(excludeUserId);
        }

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 将ResultSet转换为User对象
     */
    private User convertToUser(ResultSet rs) throws SQLException {
        User user = new User();
        user.setUserId(rs.getInt("user_id"));
        user.setUsername(rs.getString("username"));
        user.setPassword(rs.getString("password"));
        user.setRoleId(rs.getInt("role_id"));
        user.setRealName(rs.getString("real_name"));
        user.setEmail(rs.getString("email"));
        user.setCreateTime(rs.getTimestamp("create_time"));
        user.setUpdateTime(rs.getTimestamp("update_time"));
        return user;
    }

    /**
     * 根据角色ID查询用户
     */
    public List<User> findByRole(Integer roleId) {
        String sql = "SELECT * FROM user WHERE role_id = ? ORDER BY user_id DESC";
        List<User> userList = new ArrayList<>();

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, roleId);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                userList.add(convertToUser(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userList;
    }
}